<?php

use think\Loader;
use think\Request;
use think\Validate;
use think\Db;

/**
 * 检查表单令牌
 * @param string $token 表单令牌
 * @return      bool
 * */
function checkToken($token)
{
    $data = ['__token__' => $token];
    $rule = ['__token__' => 'token'];
    $msg = ['__token__' => '请不要刷新该页面！'];
    //引入验证类
    $validate = Validate::make($rule, $msg);
    if ($validate->check($data)) {
        return true;
    } else {
        return false;
    }
}

/**
 * 按综合方式输出通信数据（后台异步返回信息专用）
 * @param integer $result 状态码
 * @param string  $msg    提示信息
 * @param array   $url    参考 url() 方法；链接：https://www.kancloud.cn/manual/thinkphp5/118041
 * @param int     $timer  跳转时间
 * @return      string
 */
function adminReturn($result, $msg = '', $url = '', $timer = 1000)
{
    if ($result == 1) {
        $message = array(
            'result' => $result,
            'msg' => $msg,
            'url' => $url,
            'timer' => intval($timer)
        );
    } else {
        $request = Request::instance();
        $token = $request->token();

        $message = array(
            'result' => $result,
            'msg' => $msg,
            'url' => $url,
            'token' => $token,
            'timer' => intval($timer)
        );
    }

    echo json_encode($message, JSON_UNESCAPED_UNICODE);
    exit;
}

/**
 * REDIRECT_URL处理
 * */
function doRedirectUrl()
{
    $redirect_url = $_SERVER['REDIRECT_URL'];
    if ($redirect_url == '/admin') {
        return '/admin/index/index.html';
    } else {
        $redirect_url_arr = explode('/', $redirect_url);
        return '/' . $redirect_url_arr[1] . '/' . $redirect_url_arr[2] . '/' . $redirect_url_arr[3];
    }
}

/**
 * 获取顶级菜单ID
 * @param int $id 子菜单id
 * */
function getTopId($id)
{
    $info = Db::name('menu')->field('id,pid,level')->where(['id' => $id])->find();
    if ($info['level'] > 1) {
        return getTopId($info['pid']);
    } else {
        return $info['id'];
    }
}

/**
 * 点击顶部菜单获取匹配的左侧菜单
 * @param string $id 顶部菜单id
 * @return      bool
 * */
function showLeftMenu($id)
{
    $request_uri = $_SERVER['REDIRECT_URL'] == '/admin' ? '/admin/index/index' : doRedirectUrl();

    $info = Db::name('menu')->field('id,pid')->where(['url' => str_replace('.html', '', $request_uri)])->find();
    $top_id = getTopId($info['id']);

    if ($top_id == $id) {
        return true;
    }

    return false;
}

/**
 * 菜单高亮
 * @param int $id    id
 * @param int $level 1：顶部菜单，2：左侧分组菜单，3：左侧分组子菜单
 * @return      bool
 * */
function leftMenuActive($id, $level)
{
    $request_uri = $_SERVER['REDIRECT_URL'] == '/admin' ? '/admin/index/index' : doRedirectUrl();

    if ($level == 2) {
        $count = Db::name('menu')->where(['pid' => $id, 'is_show' => 1, 'level' => $level])->count();
        if ($count > 0) {
            //有显示的子菜单
            $info = Db::name('menu')->field('pid')->where(['url' => str_replace('.html', '', $request_uri), 'pid' => ['gt', 0]])->find();
            if ($info['pid'] == $id) {
                return true;
            } else {
                $group_info = Db::name('menu')->field('pid')->where(['id' => $info['pid']])->find();
                if ($group_info['pid'] == $id) {
                    return true;
                }
            }
        } else {
            //无显示的子菜单
            $info = Db::name('menu')->field('id,pid')->where(['url' => str_replace('.html', '', $request_uri), 'pid' => ['gt', 0]])->find();
            if ($info['id'] == $id) {
                return true;
            } else {
                if ($info['pid'] == $id) {
                    return true;
                } else {
                    $group_info = Db::name('menu')->field('pid')->where(['id' => $info['pid']])->find();
                    if ($group_info['pid'] == $id) {
                        return true;
                    }
                }
            }
        }
    } else if ($level == 3) {
        $info = Db::name('menu')->field('id')->where(['url' => str_replace('.html', '', $request_uri), 'pid' => ['gt', 0], 'level' => $level])->find();
        if ($info['id'] == $id) {
            return true;
        } else {
            $parent_info = Db::name('menu')->field('pid')->where(['url' => str_replace('.html', '', $request_uri), 'pid' => ['gt', 0], 'level' => 4])->find();
            if ($parent_info['pid'] == $id) {
                return true;
            }
        }
    }
}

/**
 * 更新URL（顶部菜单，分组菜单）
 * 调用时机：当对level=3的菜单操作完成后，执行该方法
 * */
function renewTopAndGroupUrl()
{
    //顶部菜单
    $top_list = Db::name('menu')->field('id,url')->where(['pid' => 0, 'level' => 1, 'status' => 1, 'is_show' => 1])->order('sort asc')->select();
    if ($top_list) {
        foreach ($top_list as &$v) {
            //分组菜单
            $group_list = Db::name('menu')->field('id,pid,url')->where(['pid' => $v['id'], 'level' => 2, 'status' => 1, 'is_show' => 1])->order('sort asc')->select();
            if ($group_list) {
                foreach ($group_list as $key => &$val) {
                    //第一个子菜单
                    $son_first_info = Db::name('menu')->field('id,pid,url')->where(['pid' => $val['id'], 'level' => 3, 'status' => 1, 'is_show' => 1])->order('sort asc')->find();

                    if ($son_first_info) {
                        //更新分组菜单URL
                        Db::name('menu')->where(['id' => $val['id']])->update(['url' => $son_first_info['url'], 'update_time' => time()]);
                        //更新顶部菜单URL
                        if ($key == 0) Db::name('menu')->where(['id' => $v['id']])->update(['url' => $son_first_info['url'], 'update_time' => time()]);
                    } else {
                        //更新顶部菜单URL
                        if ($key == 0) Db::name('menu')->where(['id' => $v['id']])->update(['url' => $val['url'], 'update_time' => time()]);
                    }
                }
            }
        }
    }
}

/**
 * 获取上级菜单名称
 * @param int $pid 上级菜单id
 * @return      string
 **/
function getParentMenuName($pid)
{
    if ($pid > 0) {
        $info = model('menu')->get($pid);

        return $info ? $info->name : '无';
    } else {
        return '无';
    }
}

/**
 * 获取上级菜单ID
 * @param int $pid 上级菜单id
 * @return      int
 **/
function getParentMenuId($pid)
{
    if ($pid > 0) {
        $info = model('menu')->get($pid);

        return $info ? $info->id : 0;
    } else {
        return 0;
    }
}

/**
 * 获取访问授权未选中的父级菜单ID
 * @param int   $id    菜单id
 * @param array $rules 待加入未选中父级菜单ID数组
 * */
function getNoCheckParentId($id, &$rules)
{
    $pid = Db::name('menu')->where(['id' => $id])->column('pid');
    if ($pid[0] > 0) {
        $parent_id = getParentMenuId($pid[0]);
        if ($parent_id > 0 && !in_array($parent_id, $rules)) {
            $rules[] = $parent_id;
            return getNoCheckParentId($parent_id, $rules);
        } else {
            return $rules;
        }
    }
}

/**
 * 判断访问授权复选框是否选中
 * @param int $group_id 全选分组id
 * @param int $menu_id  菜单ID
 * */
function authChecked($group_id, $menu_id)
{
    $rules = Db::name('auth_group')->where(['id' => $group_id])->column('show_rules');
    $rules = $rules[0];

    if (in_array($menu_id, explode(',', $rules))) {
        return 'checked';
    } else {
        return '';
    }
}

/**
 * 获取用户姓名
 * */
function getUsername($user_id = 0)
{
    $username = '';
    if ($user_id) $username = Db::name('user')->where(['id' => $user_id])->value('username');

    return $username;
}

/**
 * 转换utf8
 * @param $str
 * @return int|string
 */
function convertUTF8($str)
{
    if (empty($str)) {
        if (is_numeric($str)) {
            return 0;
        } else {
            return "";
        }

    } else {
        $encode = mb_detect_encoding($str, array("ASCII", 'UTF-8', "GB2312", "GBK", 'BIG5'));
        if ($encode == 'UTF-8') {
            return $str;
        } else {
            return mb_convert_encoding($str, 'UTF-8', $encode);
        }
    }
}

/**
 * 获取销售奖励数据
 * */
function getSalesBonusList()
{
    $list = Db::name('sales_bonus')->order('id desc')->select();
    if($list){
        foreach($list as &$v){
            $v['date'] = $v['year'] . '-' . $v['month'];
            $v['rate'] = $v['rate'] . '%';
        }
    }
    return $list;
}

/**
 * 获取退休基金数据
 * */
function getRetireFundList()
{
    $list = Db::name('retire_fund')->order('id desc')->select();
    if($list){
        foreach($list as &$v){
            $v['gear'] = doGear($v['gear']);
            $v['date'] = $v['year'] . '-' . $v['month'];
            $v['rate_all_price'] = $v['all_price'] * $v['rate'] / 100;
            $v['rate'] = $v['rate'] . '%';
        }
    }
    return $list;
}

/**
 * 获取门店补贴列表数据
 * */
function getShopSubsidyList($status = 0)
{
    if ($status) $where['status'] = $status;

    $list = Db::name('shop_subsidy')->where($where)->order('status asc,create_time desc')->select();
    if($list){
        foreach($list as &$val){
            $val['date'] = $val['year'] . '-' . $val['month'];
            $val['status'] = $val['status'] == 1 ? '待结算' : '已结算';
            $val['rate'] = $val['rate'] . '%';
            $val['shop_name'] = Db::name('shop')->where(['id' => $val['shop_id']])->value('name');
            $val['username'] = Db::name('user')->where(['id' => $val['user_id']])->value('username');
        }
    }

    return $list;
}

/**
 * 获取财务统计数据
 * @param date $start_time 开始时间
 * @param date $end_time   结束时间
 * */
function getCensusProfit($start_time = '', $end_time = '')
{
    if (!empty($start_time) && empty($end_time)) {
        $all_consume_where["complete_time"] = ["egt", strtotime($start_time)];
        $all_consume_no_get_where["create_time"] = ["egt", strtotime($start_time)];
        $all_withdraw_where["create_time"] = ["egt", strtotime($start_time)];
        $all_recharge_where['create_time'] = ["egt", strtotime($start_time)];
        $all_send_profit_where['create_time'] = ["egt", strtotime($start_time)];
    } elseif (empty($start_time) && !empty($end_time)) {
        $all_consume_where["complete_time"] = ["elt", strtotime($end_time) + 60 * 60 * 24 - 1];
        $all_consume_no_get_where["create_time"] = ["elt", strtotime($end_time) + 60 * 60 * 24 - 1];
        $all_withdraw_where["create_time"] = ["elt", strtotime($end_time) + 60 * 60 * 24 - 1];
        $all_recharge_where['create_time'] = ["elt", strtotime($end_time) + 60 * 60 * 24 - 1];
        $all_send_profit_where['create_time'] = ["elt", strtotime($end_time) + 60 * 60 * 24 - 1];
    } elseif (!empty($start_time) && !empty($end_time)) {
        $all_consume_where["complete_time"] = ['between', [strtotime($start_time), strtotime($end_time) + 60 * 60 * 24 - 1]];
        $all_consume_no_get_where["create_time"] = ['between', [strtotime($start_time), strtotime($end_time) + 60 * 60 * 24 - 1]];
        $all_withdraw_where["create_time"] = ['between', [strtotime($start_time), strtotime($end_time) + 60 * 60 * 24 - 1]];
        $all_recharge_where["create_time"] = ['between', [strtotime($start_time), strtotime($end_time) + 60 * 60 * 24 - 1]];
        $all_send_profit_where["create_time"] = ['between', [strtotime($start_time), strtotime($end_time) + 60 * 60 * 24 - 1]];
    }

    // 总累计销售额
    $all_consume_where['is_pay'] = 1;        //已付款
    $all_consume_where['back_order_sn'] = '';//未退款的
    $all_consume = Db::name('order')->where($all_consume_where)->sum('pay_price');

    // 总销售额
    $all_consume_no_get_where['complete_time'] = ['gt', 0];
    $all_consume_no_get = Db::name('order')->where($all_consume_no_get_where)->sum('pay_price');

    // 应发收益总统计
    $all_send_profit = Db::name('profit')->where($all_send_profit_where)->where(['type' => ['IN', [1, 2, 3, 6, 7, 9]]])->sum('price');

    // 成功提现总额度统计
    $all_withdraw = Db::name('profit')->where($all_withdraw_where)->where(['type' => 5])->sum('price');

    // 充值总金额
    $all_recharge = Db::name('profit_change')->where($all_recharge_where)->sum('price');

    $list[] = [
        'all_consume' => sprintf("%.2f", $all_consume),
        'all_consume_no_get' => sprintf("%.2f", $all_consume_no_get),
        'all_send_profit' => sprintf("%.2f", $all_send_profit),
        'all_withdraw' => sprintf("%.2f", $all_withdraw),
        'all_recharge' => sprintf("%.2f", $all_recharge),
    ];

    return $list;
}

/**
 * 获取开支明细数据（一级）
 * @param date $start_time 开始时间
 * @param date $end_time   结束时间
 * */
function getSpendCensusList($start_time = '', $end_time = '')
{
    if (!empty($start_time) && empty($end_time)) {
        $where["create_time"] = ["egt", strtotime($start_time)];
    } elseif (empty($start_time) && !empty($end_time)) {
        $where["create_time"] = ["elt", strtotime($end_time) + 60 * 60 * 24 - 1];
    } elseif (!empty($start_time) && !empty($end_time)) {
        $where["create_time"] = ['between', [strtotime($start_time), strtotime($end_time) + 60 * 60 * 24 - 1]];
    }

    $list = Db::name('profit')->distinct(true)->field('FROM_UNIXTIME( create_time, "%Y-%m-%d") date')->where($where)->where(['type' => ['IN', [1, 2, 3, 6, 7, 9]], 'status' => ['neq', -1]])->order('create_time desc')->select();
    if ($list) {
        foreach ($list as &$v) {
            // 消费收益
            $consume_profit = dayAllProfit($v['date'], 1);
            $v['consume_profit'] = $consume_profit;
            // 绩效收益
            $achievements_profit = dayAllProfit($v['date'], 2);
            $v['achievements_profit'] = $achievements_profit;
            // 服务津贴
            $service_profit = dayAllProfit($v['date'], 3);
            $v['service_profit'] = $service_profit;
            // 门店区域补贴
            $shop_profit = dayAllProfit($v['date'], 9);
            $v['shop_profit'] = $shop_profit;
            // 销售奖励
            $sales_profit = dayAllProfit($v['date'], 6);
            $v['sales_profit'] = $sales_profit;

            $start_time = strtotime($v['date']);
            $end_time = $start_time + 60 * 60 * 24 - 1;

            $where['create_time'] = ['between', [$start_time, $end_time]];

            // 一档退休基金
            $first_retire_profit = Db::name('retire_fund')->where($where)->where(['gear' => 'first_gear'])->value('all_retire_price');
            $v['first_retire_profit'] = sprintf('%.2f', $first_retire_profit);
            // 二档退休基金
            $second_retire_profit = Db::name('retire_fund')->where($where)->where(['gear' => 'second_gear'])->value('all_retire_price');
            $v['second_retire_profit'] = sprintf('%.2f', $second_retire_profit);
            // 三档退休基金
            $third_retire_profit = Db::name('retire_fund')->where($where)->where(['gear' => 'third_gear'])->value('all_retire_price');
            $v['third_retire_profit'] = sprintf('%.2f', $third_retire_profit);

            $all_price = $consume_profit + $achievements_profit + $service_profit + $shop_profit + $sales_profit + $first_retire_profit + $second_retire_profit + $third_retire_profit;
            $v['all_price'] = sprintf('%.2f', $all_price);
        }
    }

    return $list;
}

/**
 * 获取开支明细数据（二级）
 * @param date   $date     日期
 * @param string $username 用户昵称
 * @param string $mobile   手机号
 * */
function getSpendCensusUserList($date, $username = '', $mobile = '')
{
    if (empty($date)) adminReturn(2, '日期缺失');

    $start_time = strtotime($date);
    $end_time = $start_time + 60 * 60 * 24 - 1;

    if ($mobile) $search_where['u.mobile'] = ['like', '%' . $mobile . '%'];
    if ($username) $search_where['u.username'] = ['like', '%' . $username . '%'];

    $list = Db::name('profit p')->join('shop_user u', 'p.user_id = u.id', 'left')
        ->distinct(true)->field('p.user_id,FROM_UNIXTIME( p.create_time, "%Y-%m-%d") date')
        ->where($search_where)
        ->where(['p.type' => ['IN', [1, 2, 3, 6, 7, 9]], 'p.status' => ['neq', -1], 'p.create_time' => ['between', [$start_time, $end_time]]])
        ->order('p.create_time desc')
        ->select();

    if ($list) {
        foreach ($list as &$v) {
            // 消费收益
            $consume_profit = userAllProfit($v['user_id'], $v['date'], 1);
            $v['consume_profit'] = $consume_profit;
            // 绩效收益
            $achievements_profit = userAllProfit($v['user_id'], $v['date'], 2);
            $v['achievements_profit'] = $achievements_profit;
            // 服务津贴
            $service_profit = userAllProfit($v['user_id'], $v['date'], 3);
            $v['service_profit'] = $service_profit;
            // 门店区域补贴
            $shop_profit = userAllProfit($v['user_id'], $v['date'], 9);
            $v['shop_profit'] = $shop_profit;
            // 销售奖励
            $sales_profit = userAllProfit($v['user_id'], $v['date'], 6);
            $v['sales_profit'] = $sales_profit;

            // 退休基金
            $retire_profit = userAllProfit($v['user_id'], $v['date'], 7);

            $user_info = Db::name('user')->field('gear,mobile,username')->where(['id' => $v['user_id']])->find();
            $gear = $user_info['gear'];
            switch ($gear) {
                case 'first_gear':
                    $v['first_retire_profit'] = $retire_profit;
                    $v['second_retire_profit'] = '0.00';
                    $v['third_retire_profit'] = '0.00';
                    break;
                case 'second_gear':
                    $v['first_retire_profit'] = '0.00';
                    $v['second_retire_profit'] = $retire_profit;
                    $v['third_retire_profit'] = '0.00';
                    break;
                case 'third_gear':
                    $v['first_retire_profit'] = '0.00';
                    $v['second_retire_profit'] = '0.00';
                    $v['third_retire_profit'] = $retire_profit;
                    break;
                default:
                    $v['first_retire_profit'] = '0.00';
                    $v['second_retire_profit'] = '0.00';
                    $v['third_retire_profit'] = '0.00';
                    break;
            }
            $v['mobile'] = $user_info['mobile'];
            $v['username'] = $user_info['username'];

            $all_price = $consume_profit + $achievements_profit + $service_profit + $shop_profit + $sales_profit + $retire_profit;
            $v['all_price'] = sprintf('%.2f', $all_price);
        }
    }

    return $list;
}

/**
 * 获取开支明细数据（三级）
 * */
function getSpendCensusUserDetail($user_id, $date, $type = 0)
{
    if (empty($user_id)) adminReturn(2, '用户ID缺失');
    if (empty($date)) adminReturn(2, '日期缺失');

    if ($type) {
        $where['type'] = $type;
    } else {
        $where['type'] = ['IN', [1, 2, 3, 6, 7, 9]];
    }

    $start_time = strtotime($date);
    $end_time = $start_time + 60 * 60 * 24 - 1;

    $list = Db::name('profit')->field('order_id,type,price,create_time,remark')->where($where)->where(['user_id' => $user_id, 'create_time' => ['between', [$start_time, $end_time]]])->order('create_time asc')->select();
    if ($list) {
        foreach ($list as &$v) {
            $user_info = Db::name('user u')->join('shop_order o', 'u.id = o.user_id', 'left')
                ->field('u.id,u.username,u.mobile,u.gear,o.is_first_order')->where(['o.id' => $v['order_id']])->find();

            $v['username'] = $user_info['username'];
            $v['mobile'] = $user_info['mobile'];

            $v['create_time'] = timeFormat($v['create_time']);

            switch ($v['type']) {
                case 1:
                    $count = 0;
                    getTreeCount($user_id, $user_info['id'], $count);
                    $type_desc = '消费收益';
                    $desc = ' 消费收益 ' . ($count > 0 ? '下' . $count . ' ' : '') . '[' . ($user_info['is_first_order'] ? '复消' : '首消') . ']';
                    break;
                case 2:
                    $count = 0;
                    getTreeCount($user_id, $user_info['id'], $count);
                    $type_desc = '绩效收益';
                    $desc = ' 绩效收益 ' . ($count > 0 ? '下' . $count . ' ' : '') . '[' . ($user_info['is_first_order'] ? '复消' : '首消') . ']';;
                    break;
                case 3:
                    $count = 0;
                    getTreeCount($user_id, $user_info['id'], $count);
                    $type_desc = '服务津贴';
                    $desc = '服务津贴 ' . ($count > 0 ? '下' . $count . ' ' : '') . '[' . ($user_info['is_first_order'] ? '复消' : '首消') . ']';;
                    break;
                case 6:
                    $type_desc = '销售奖励';
                    $desc = $v['remark'];
                    break;
                case 7:
                    $gear = $user_info['gear'];
                    $type_desc = ($gear == 'first_gear' ? '一档' : ($gear == 'second_gear' ? '二档' : '三挡')) . '退休基金';
                    $desc = $type_desc;
                    break;
                case 9:
                    $type_desc = '门店区域补贴';
                    $desc = $v['remark'];
                    break;
            }


            $v['type_desc'] = $type_desc;
            $v['username'] = $user_info['username'];
            $v['desc'] = $desc;
        }
    }
    return $list;
}


/**
 * 获取统计查询数据
 * @param date $start_time 开始时间
 * @param date $end_time   结束时间
 * @return array
 * */
function getCensusSearchData($start_time = '', $end_time = '')
{
    if (!empty($start_time) && empty($end_time)) {
        $order_where["pay_time"] = ["egt", strtotime($start_time)];
        $profit_where["create_time"] = ["egt", strtotime($start_time)];
    } elseif (empty($start_time) && !empty($end_time)) {
        $order_where["pay_time"] = ["elt", strtotime($end_time) + 60 * 60 * 24 - 1];
        $profit_where["create_time"] = ["elt", strtotime($end_time) + 60 * 60 * 24 - 1];
    } elseif (!empty($start_time) && !empty($end_time)) {
        $order_where["pay_time"] = ['between', [strtotime($start_time), strtotime($end_time) + 60 * 60 * 24 - 1]];
        $profit_where["create_time"] = ['between', [strtotime($start_time), strtotime($end_time) + 60 * 60 * 24 - 1]];
    }

    $order_where['is_pay'] = 1;
    $order_where['is_del'] = 0;
    $order_where['back_order_sn'] = '';
    $order_time_list = Db::name('order')->distinct(true)->field('FROM_UNIXTIME( pay_time, "%Y-%m-%d") date')->where($order_where)->order('pay_time desc')->select();

    $profit_where['type'] = ['IN', [1, 2, 3, 4, 5, 6, 7, 9, 10, 11]];
    $profit_where['status'] = ['neq', -1];
    $profit_time_list = Db::name('profit')->distinct(true)->field('FROM_UNIXTIME(create_time, "%Y-%m-%d") date')->where($profit_where)->order('create_time desc')->select();

    $time_list = array_unique(array_merge(array_column($order_time_list, 'date'), array_column($profit_time_list, 'date')));

    if ($time_list) {
        $tmp_data = [];
        foreach ($time_list as $v) {
            $tmp_data[]['date'] = $v;
        }

        $tmp_sql = 'CREATE TEMPORARY TABLE shop_tmp(date varchar(100))';
        Db::query($tmp_sql);

        Db::name('tmp')->insertAll($tmp_data);

        $list = Db::name('tmp')->order('date desc')->select();
        if ($list) {
            foreach ($list as &$v) {
                $start_time = strtotime($v['date']);
                $end_time = $start_time + 60 * 60 * 24 - 1;

                // 销售总额
                $each_order_where['pay_time'] = ['between', [$start_time, $end_time]];
                $each_order_where['is_pay'] = 1;
                $each_order_where['is_del'] = 0;
                $each_order_where['back_order_sn'] = '';
                $all_order_price = Db::name('order')->where($each_order_where)->sum('pay_price');
                $v['all_order_price'] = sprintf($all_order_price);

                $each_profit_where['create_time'] = ['between', [$start_time, $end_time]];
                $each_profit_where['status'] = ['neq', -1];
                // 应发收益统计
                $all_get_profit = Db::name('profit')->where($each_profit_where)->where(['type' => ['IN', [1, 2, 3, 6, 7, 9]]])->sum('price');
                $v['all_get_profit'] = sprintf($all_get_profit);

                // 冻结收益总额
                $all_frozen_profit = Db::name('profit')->where(['create_time' => ['between', [$start_time, $end_time]], 'status' => 1])->sum('price');
                $v['all_frozen_profit'] = sprintf($all_frozen_profit);

                // 充值总金额
                $all_recharge_profit = Db::name('profit')->where($each_profit_where)->where(['type' => 10])->sum('price');
                $v['all_recharge_profit'] = sprintf($all_recharge_profit);

                // 可用收益支付总额
                $all_pay_profit = Db::name('profit')->where($each_profit_where)->where(['type' => 4])->sum('price');
                $v['all_pay_profit'] = sprintf($all_pay_profit);

                // 微信支付总额
                $all_wx_pay_price = Db::name('order')->where($each_order_where)->where(['pay_type' => 1])->sum('pay_price');
                $v['all_wx_pay_price'] = sprintf($all_wx_pay_price);

                // 扣款总额
                $all_deduct_profit = Db::name('profit')->where($each_profit_where)->where(['type' => 11])->sum('price');
                $v['all_deduct_profit'] = sprintf($all_deduct_profit);

                // 成功提现总额
                $all_withdraw_profit = Db::name('profit')->where($each_profit_where)->where(['type' => 5])->sum('price');
                $v['all_withdraw_profit'] = sprintf($all_withdraw_profit);
            }
        }

        $all_list = Db::name('tmp')->order('date desc')->select();
        $all_start_date = '';
        $all_end_date = '';
        foreach ($all_list as $k => &$v) {
            if ($k == 0) {
                $all_end_date = $v['date'];
            } else if ($k + 1 == count($all_list)) {
                $all_start_date = $v['date'];
            }

            $start_time = strtotime($v['date']);
            $end_time = $start_time + 60 * 60 * 24 - 1;

            // 销售总额
            $each_order_where['pay_time'] = ['between', [$start_time, $end_time]];
            $each_order_where['is_pay'] = 1;
            $each_order_where['is_del'] = 0;
            $each_order_where['back_order_sn'] = '';
            $all_order_price = Db::name('order')->where($each_order_where)->sum('pay_price');
            $v['all_order_price'] = sprintf($all_order_price);

            $each_profit_where['create_time'] = ['between', [$start_time, $end_time]];
            $each_profit_where['status'] = ['neq', -1];
            // 应发收益统计
            $all_get_profit = Db::name('profit')->where($each_profit_where)->where(['type' => ['IN', [1, 2, 3, 6, 7, 9]]])->sum('price');
            $v['all_get_profit'] = sprintf($all_get_profit);

            // 冻结收益总额
            $all_frozen_profit = Db::name('profit')->where(['create_time' => ['between', [$start_time, $end_time]], 'status' => 1])->sum('price');
            $v['all_frozen_profit'] = sprintf($all_frozen_profit);

            // 成功提现总额
            $all_withdraw_profit = Db::name('profit')->where($each_profit_where)->where(['type' => 5])->sum('price');
            $v['all_withdraw_profit'] = sprintf($all_withdraw_profit);

            // 充值总金额
            $all_recharge_profit = Db::name('profit')->where($each_profit_where)->where(['type' => 10])->sum('price');
            $v['all_recharge_profit'] = sprintf($all_recharge_profit);

            // 可用收益支付总额
            $all_pay_profit = Db::name('profit')->where($each_profit_where)->where(['type' => 4])->sum('price');
            $v['all_pay_profit'] = sprintf($all_pay_profit);

            // 微信支付的总额
            $all_wx_pay_price = Db::name('order')->where($each_order_where)->where(['pay_type' => 1])->sum('pay_price');
            $v['all_wx_pay_price'] = sprintf($all_wx_pay_price);

            // 扣款总额
            $all_deduct_profit = Db::name('profit')->where($each_profit_where)->where(['type' => 11])->sum('price');
            $v['all_deduct_profit'] = sprintf($all_deduct_profit);
        }
        $census = [
            'date' => ($all_start_date) . ' - ' . ($all_end_date),
            'all_order_price' => $all_list ? sprintf('%.2f', array_sum(array_column($all_list, 'all_order_price'))) : '0.00',
            'all_get_profit' => $all_list ? sprintf('%.2f', array_sum(array_column($all_list, 'all_get_profit'))) : '0.00',
            'all_frozen_profit' => $all_list ? sprintf('%.2f', array_sum(array_column($all_list, 'all_frozen_profit'))) : '0.00',
            'all_withdraw_profit' => $all_list ? sprintf('%.2f', array_sum(array_column($all_list, 'all_withdraw_profit'))) : '0.00',
            'all_recharge_profit' => $all_list ? sprintf('%.2f', array_sum(array_column($all_list, 'all_recharge_profit'))) : '0.00',
            'all_pay_profit' => $all_list ? sprintf('%.2f', array_sum(array_column($all_list, 'all_pay_profit'))) : '0.00',
            'all_wx_pay_price' => $all_list ? sprintf('%.2f', array_sum(array_column($all_list, 'all_wx_pay_price'))) : '0.00',
            'all_deduct_profit' => $all_list ? sprintf('%.2f', array_sum(array_column($all_list, 'all_deduct_profit'))) : '0.00',
        ];

        $list = array_merge([$census], $list);

        return $list;
    }
}

/**
 * 获取列标
 * @param int $length 列标长度
 * @return array
 * */
function getLetterList($length)
{
    $arr = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];
    return array_slice($arr, 0, $length);
}

/**
 * 导出excel
 * @param string $title 文件名
 * @param array  $th    表头；例 ==> ['时间', '销售总额', '应发收益统计', '冻结收益总额', '充值总金额', '可用收益支付总额']
 * @param array  $td    数据下标；例 ==> ['date', 'all_order_price', 'all_get_profit', 'all_frozen_profit', 'all_recharge_profit', 'all_pay_profit']
 * @param array  $data  数据
 * */
function exportExcel($title, $th, $td, $data)
{
    // 引入类库
    Loader::import('phpexcel\PHPExcel', EXTEND_PATH);

    // 文件名和文件类型
    $fileName = $title;
    $fileType = "xls";

    $obj = new \PHPExcel();

    // 设置当前sheet
    $obj->setActiveSheetIndex(0);

    // 设置当前sheet的名称
    $obj->getActiveSheet()->setTitle($title);

    $list = getLetterList(count($th));

    // 填充第一行数据
    $activeSheet = $obj->getActiveSheet();
    for ($i = 0; $i < count($list); $i++) {
        $activeSheet->setCellValue($list[$i] . '1', $th[$i]);
    }
    // 填充第n(n>=2, n∈N*)行数据
    $data_length = count($data);
    for ($i = 0; $i < $data_length; $i++) {
        for ($j = 0; $j < count($td); $j++) {
            $activeSheet->setCellValue($list[$j] . ($i + 2), $data[$i][$td[$j]]);
        }
    }

    // 设置加粗和左对齐
    foreach ($list as $col) {
        // 设置第一行加粗
        $activeSheet->getStyle($col . '1')->getFont()->setBold(true);
        // 设置第1-n行，居中对齐
        for ($i = 1; $i <= $data_length + 1; $i++) {
            $activeSheet->getStyle($col . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        }
    }

    // 设置列宽
    for ($i = 0; $i < count($list); $i++) {
        $activeSheet->getColumnDimension($list[$i])->setWidth(25);
    }

    // 导出
    ob_clean();
    if ($fileType == 'xls') {
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $fileName . '.xls');
        header('Cache-Control: max-age=1');
        $objWriter = new \PHPExcel_Writer_Excel5($obj);
        $objWriter->save('php://output');
        exit;
    } elseif ($fileType == 'xlsx') {
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx');
        header('Cache-Control: max-age=1');
        $objWriter = \PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
        $objWriter->save('php://output');
        exit;
    }
}

